﻿using Model;
using Newtonsoft.Json.Linq;
using System;
using System.Data;

namespace BLL
{
    /// <summary>
    /// 公告
    /// </summary>
    public class SYS_ANNOUNCEMENTBLL : BaseBLL
    {
        public DataTable GetListByPage(string whereStr, string orderby, int startIndex, int endIndex)
        {
            string sql = string.Format(@"SELECT *
  FROM (select ROW_NUMBER() OVER(ORDER BY T1.n_id ASC) as r,
               T1.n_id as n_id,
               T1.c_name as c_name,
               T1.c_content as c_content,
               T1.c_url as c_url,
               T1.n_userid as n_userid,
               T3.USER_NAME AS USERNAME,
               to_char(T1.d_starttime,'YYYY-MM-DD HH24:MI:SS') as d_starttime,
               T1.c_remark as c_remark,
               T1.n_companyid as n_companyid,
               T2.CMY_NAME AS COMPANYNAME,
               to_char(T1.d_endtime,'YYYY-MM-DD HH24:MI:SS') as d_endtime,
               DECODE(T1.n_state, 0, '无效', 1, '有效', '其它') as n_state,
               DECODE(T1.N_TYPE, 0, '系统消息', 1, '发布消息', '其它') as N_TYPE
          from sys_announcement T1
          LEFT JOIN sys_company T2
            ON T1.N_COMPANYID = T2.CMY_ID
          LEFT JOIN SYS_USER T3
            ON T1.N_USERID = T3.USER_ID
         WHERE 1 = 1 ");

            if (!string.IsNullOrEmpty(whereStr))
            {
                sql += whereStr;
            }

            if (!string.IsNullOrEmpty(orderby))
            {
                sql += " order by " + orderby;
            }

            sql += @" ) TT";
            if (startIndex >= 2)
            {
                sql += " WHERE TT.r BETWEEN (" + startIndex + "-1)*" + endIndex + "+1 AND " + startIndex + "*" + endIndex + "";
            }
            else
            {
                sql += " WHERE TT.r BETWEEN (" + startIndex + "-1)*" + endIndex + " AND " + startIndex + "*" + endIndex + "";
            }

            return Query(sql, "", "GetListByPage", "查询队列", "");
        }

        public int GetRecordCount(string whereStr, string operName)
        {
            string sql = string.Format(@"select COUNT(1)
  from sys_announcement T1
  LEFT JOIN sys_company T2
    ON T1.N_COMPANYID = T2.CMY_ID
  LEFT JOIN SYS_USER T3
    ON T1.N_USERID = T3.USER_ID
 WHERE 1 = 1 " + whereStr);

            int count = GetCount(sql);

            return count;
        }

        /// <summary>
        /// 获取最新公告
        /// </summary>
        public DataTable GetNotice(string startTime, string companyId)
        {
            string sql = string.Format(@"select T1.n_id as n_id,
               T1.c_name as c_name,
               T1.c_content as c_content,
               T1.c_url as c_url,
               T1.n_userid as n_userid,
               T3.USER_NAME AS USERNAME,
               to_char(T1.d_starttime,'YYYY-MM-DD HH24:MI:SS') as d_starttime,
               T1.c_remark as c_remark,
               T1.n_companyid as n_companyid,
               T2.CMY_NAME AS COMPANYNAME,
               to_char(T1.d_endtime,'YYYY-MM-DD HH24:MI:SS') as d_endtime,
               DECODE(T1.n_state, 0, '无效', 1, '有效', '其它') as n_state,
               DECODE(T1.N_TYPE, 0, '系统消息', 1, '发布消息', '其它') as N_TYPE
          from sys_announcement T1
          LEFT JOIN sys_company T2
            ON T1.N_COMPANYID = T2.CMY_ID
          LEFT JOIN SYS_USER T3
            ON T1.N_USERID = T3.USER_ID
         WHERE 1 = 1 ");

            if (!string.IsNullOrEmpty(startTime))
            {
                sql += "and T1.N_STATE = 1 and T1.D_ENDTIME >= " + startTime;
            }

            if (!string.IsNullOrEmpty(companyId))
            {
                sql += " and T1.n_companyid = " + companyId;
            }

            return Query(sql += " order by  D_STARTTIME desc ", "", "GetListByPage", "查询队列", "");
        }

        /// <summary>
        /// 根据编号查询公告
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public SYS_ANNOUNCEMENT SelectById(string id)
        {
            string sql = string.Format(@"select * from SYS_ANNOUNCEMENT a where a.N_ID= " + id);

            DataTable dt = Query(sql);

            SYS_ANNOUNCEMENT model = null;
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    model = new SYS_ANNOUNCEMENT();
                    model.N_ID = Convert.ToDecimal(id);
                    model.N_COMPANYID = decimal.Parse(dr["N_COMPANYID"].ToString());
                    model.N_USERID = decimal.Parse(dr["N_USERID"].ToString());
                    model.N_STATE = decimal.Parse(dr["N_STATE"].ToString());
                    model.N_TYPE = decimal.Parse(dr["N_TYPE"].ToString());
                    model.C_NAME = dr["C_NAME"].ToString();
                    model.C_CONTENT = dr["C_CONTENT"].ToString();
                    model.C_REMARK = dr["C_REMARK"].ToString();
                    model.C_URL = dr["C_URL"].ToString();
                    model.D_STARTTIME = Convert.ToDateTime(dr["D_STARTTIME"].ToString());
                    model.D_ENDTIME = Convert.ToDateTime(dr["D_ENDTIME"].ToString());
                }
            }
            return model;
        }

        public int INSERT(SYS_ANNOUNCEMENT model, string operName)
        {
            model.N_ID = GetSequence("SEQ_ANNOUNCEMENT_ID");
            if (model.C_URL == "/Module/ParameterSettings/AnnouncementDetail.aspx?ID=1")
            {
                model.C_URL = "/Module/ParameterSettings/AnnouncementDetail.aspx?ID=" + model.N_ID;
            }
            string sql = string.Format(@"insert into sys_announcement
  (n_id,
   c_name,
   c_content,
   c_url,
   n_userid,
   d_starttime,
   c_remark,
   n_companyid,
   d_endtime,
   n_state,
   N_TYPE)
values
  ({0},
   '{1}',
   '{2}',
   '{3}',
   {4},
   TO_DATE('{5}','YYYY-MM-DD HH24:MI:SS'),
   '{6}',
   {7},
   TO_DATE('{8}','YYYY-MM-DD HH24:MI:SS'),
   {9},{10})", model.N_ID, model.C_NAME, model.C_CONTENT, model.C_URL, model.N_USERID, model.D_STARTTIME, model.C_REMARK, model.N_COMPANYID, model.D_ENDTIME, model.N_STATE, model.N_TYPE);

            int count = NoQuery(sql, "新增银行本票", "INSERT", operName);
            return count;
        }

        public int UPDATE(SYS_ANNOUNCEMENT model, string operName)
        {
            string sql = string.Format(@"update sys_announcement
   set c_name      = '{1}',
       c_content   = '{2}',
       c_url       = '{3}',
       n_userid    = {4},
       d_starttime = TO_DATE('{5}','YYYY-MM-DD HH24:MI:SS'),
       c_remark    = '{6}',
       n_companyid = {7},
       d_endtime   = TO_DATE('{8}','YYYY-MM-DD HH24:MI:SS'),
       n_state     = {9},
       N_TYPE      = {10}
 where n_id = {0}", model.N_ID, model.C_NAME, model.C_CONTENT, model.C_URL, model.N_USERID, model.D_STARTTIME, model.C_REMARK, model.N_COMPANYID, model.D_ENDTIME, model.N_STATE, model.N_TYPE);
            int count = NoQuery(sql, operName);
            return count;
        }

        public JObject GetInfoByBank(int? userId, int skip, int take)
        {
            if (userId == null)
            {
                userId = (int)User.USER_ID;
            }
            var sql = @"SELECT
                            T1.*
                        FORM ";
            return null;
        }
    }
}